package com.ph.irp.base.incomeexpense.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.incomeexpense.dao.IncomeExpenseDAO;
import com.ph.irp.model.PBaseIncomeexpense;
import com.ph.irp.model.PBasePurviewUser;
import com.ph.util.DBUtil;

public class IncomeExpenseDAOImpl implements IncomeExpenseDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseIncomeexpense> getAllIncomeExpense() {
		List<PBaseIncomeexpense> resList = new ArrayList<PBaseIncomeexpense>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select i.IncomeExpenseID id,i.IncomeExpenseCode code,");
			sb.append("i.IncomeExpenseType type,i.IncomeExpenseName name, ");
			sb
					.append("i.IncomeExpenseDesc expensedesc,i.AccountBookId acountbookid,");
			sb.append("i.Forbidden forbidden ");
			sb.append("from p_base_incomeexpense i");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseIncomeexpense incomeExpense = new PBaseIncomeexpense();
				incomeExpense.setIncomeExpenseCode(rs.getString("code"));
				incomeExpense.setIncomeExpenseDesc(rs.getString("expensedesc"));
				incomeExpense.setIncomeExpenseId(rs.getInt("id"));
				incomeExpense.setIncomeExpenseName(rs.getString("name"));
				incomeExpense.setIncomeExpenseType(rs.getInt("type"));
				incomeExpense.setPBaseAccountbookId(rs.getInt("acountbookid"));
				incomeExpense.setForbidden(rs.getInt("forbidden"));
				resList.add(incomeExpense);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return resList;
	}

	public List<PBaseIncomeexpense> getIncomeExpenseByNameAndCode(String code,
			String name) {
		List<PBaseIncomeexpense> resList = new ArrayList<PBaseIncomeexpense>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select i.IncomeExpenseID id,i.IncomeExpenseCode code,");
			sb.append("i.IncomeExpenseType type,i.IncomeExpenseName name, ");
			sb
					.append("i.IncomeExpenseDesc expensedesc,i.AccountBookId acountbookid,");
			sb.append("i.Forbidden forbidden ");
			sb.append("from p_base_incomeexpense i ");
			sb.append("where 1=1 ");
			if (code != null && !code.equals("")) {
				sb.append(" and IncomeExpenseCode like '%").append(code)
						.append("%' ");
			}

			if (name != null && !name.equals("")) {
				sb.append(" and IncomeExpenseName like '%").append(name).append(
						"%'");
			}
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseIncomeexpense incomeExpense = new PBaseIncomeexpense();
				incomeExpense.setIncomeExpenseCode(rs.getString("code"));
				incomeExpense.setIncomeExpenseDesc(rs.getString("expensedesc"));
				incomeExpense.setIncomeExpenseId(rs.getInt("id"));
				incomeExpense.setIncomeExpenseName(rs.getString("name"));
				incomeExpense.setIncomeExpenseType(rs.getInt("type"));
				incomeExpense.setPBaseAccountbookId(rs.getInt("acountbookid"));
				incomeExpense.setForbidden(rs.getInt("forbidden"));
				resList.add(incomeExpense);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return resList;
	}

	public void addIncomeExpense(PBaseIncomeexpense ie) {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into p_base_incomeexpense(IncomeExpenseCode,IncomeExpenseName,IncomeExpenseDesc,AccountBookId,IncomeExpenseType)");
			sb.append(" values('");
			sb.append(ie.getIncomeExpenseCode());
			sb.append("','").append(ie.getIncomeExpenseName());
			sb.append("','").append(ie.getIncomeExpenseDesc());
			sb.append("',").append(ie.getPBaseAccountbookId());
			sb.append(",").append(ie.getIncomeExpenseType());
			sb.append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

	}

	public PBaseIncomeexpense getIncomeExpenseByIncomeExpenseId(String expenseID) {
		PBaseIncomeexpense result = new PBaseIncomeexpense();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select i.IncomeExpenseID id,i.IncomeExpenseCode code,");
			sb.append("i.IncomeExpenseType type,i.IncomeExpenseName name, ");
			sb
					.append("i.IncomeExpenseDesc expensedesc,i.AccountBookId abookID,");
			sb.append("i.Forbidden forbidden ");
			sb.append("from p_base_incomeexpense i ");
			sb.append("where IncomeExpenseID=").append(expenseID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				result.setIncomeExpenseCode(rs.getString("code"));
				result.setIncomeExpenseDesc(rs.getString("expensedesc"));
				result.setIncomeExpenseId(rs.getInt("id"));
				result.setIncomeExpenseName(rs.getString("name"));
				result.setIncomeExpenseType(rs.getInt("type"));
				result.setPBaseAccountbookId(rs.getInt("abookID"));
				result.setForbidden(rs.getInt("forbidden"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return result;

	}

	public void editIncomeExpense(PBaseIncomeexpense ie) {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update p_base_incomeexpense set IncomeExpenseCode='");
			sb.append(ie.getIncomeExpenseCode()).append("',IncomeExpenseType=");
			sb.append(ie.getIncomeExpenseType()).append(",IncomeExpenseName='");
			sb.append(ie.getIncomeExpenseName())
					.append("',IncomeExpenseDesc='");
			sb.append(ie.getIncomeExpenseDesc()).append("',AccountBookId=");
			sb.append(ie.getPBaseAccountbookId());
			sb.append(" where IncomeExpenseID=")
					.append(ie.getIncomeExpenseId());

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public void deleteIncomeExpense(String deleteIds) {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_incomeexpense ");
			sb.append(" where IncomeExpenseID in (").append(deleteIds).append(
					")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

	}

	public void forbidIncomeExpense(String forbidId) {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update p_base_incomeexpense set Forbidden=1 ");
			sb.append("where IncomeExpenseID=").append(forbidId);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public void antiForbidIncomeExpense(String forbidId) {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update p_base_incomeexpense set Forbidden=0 ");
			sb.append("where IncomeExpenseID=").append(forbidId);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

	}
}
